DROP TABLE IF EXISTS jms_ods.yl_tms_route;
CREATE EXTERNAL TABLE jms_ods.yl_tms_route(
  id               bigint                                                     ,  -- NUMBER(11)
  start_code       string      comment  "始发网点编码"                        ,  -- NVARCHAR2(30)
  start_name       string      comment  "始发网点名称"                        ,  -- NVARCHAR2(50)
  end_code         string      comment  ""                                    ,  -- NVARCHAR2(30)
  end_name         string      comment  ""                                    ,  -- NVARCHAR2(50)
  next_code        string      comment  "下一站编码"                           ,  -- NVARCHAR2(30)
  next_name        string      comment  "下一站名称"                           ,  -- NVARCHAR2(50)
  route_code       string      comment  "路由编码（用 - 分隔）"               ,  -- NVARCHAR2(100)
  route_name       string      comment  "路由名称"                            ,  -- NVARCHAR2(100)
  route_type       tinyint     comment  "路由类型：1主用  2备用"              ,  -- NUMBER(1)
  exception_status tinyint     comment  "异常原因:1无异常  2串联失败 3无路段" ,  -- NUMBER(1)
  status           tinyint     comment  "启用状态：1启用 2禁用"               ,  -- NUMBER(1)
  create_by        bigint      comment  "创建人ID"                            ,  -- NUMBER(11)
  create_by_name   string      comment  "创建人名称"                          ,  -- NVARCHAR2(50)
  create_time      timestamp   comment  "创建时间"                            ,  -- DATE
  update_by        bigint      comment  "更新人ID"                            ,  -- NUMBER(11)
  update_by_name   string      comment  "更新人名称"                          ,  -- NVARCHAR2(50)
  update_time      timestamp   comment  "更新时间"                            ,  -- DATE
  connect_type bigint comment '1:系统  2：手工',
  EXCEPTION_DESC STRING comment '异常说明',
  START_END_NAME STRING comment  '始发、目的网点',
  START_DATE TIMESTAMP comment '生效时间',
  END_DATE TIMESTAMP comment '失效时间'
 )
 COMMENT '路由表'
 PARTITIONED BY (dt STRING COMMENT '更新/创建日期 (yyyy-MM-dd)')
 STORED AS PARQUET
 LOCATION '/dw/hive/jms_ods.db/external/yl_tms_route'
 TBLPROPERTIES (
 'discover.partitions'='false',
 'parquet.column.index.access'='true'
 );

MSCK REPAIR TABLE jms_ods.yl_tms_route;

alter table jms_ods.YL_TMS_ROUTE add columns(connect_type bigint comment '1:系统  2：手工',
EXCEPTION_DESC STRING comment '异常说明',
START_END_NAME STRING comment  '始发、目的网点',
START_DATE TIMESTAMP comment '生效时间',
END_DATE TIMESTAMP comment '失效时间' )cascade;
